#!/bin/sh
#行程表-行程维度 RANGE分区20200101 -- 20231231
table_name=app_trip_statistic_pdi

create_table_sql=$(cat <<EOF
    DROP TABLE IF EXISTS ${KUDU_DATABASE}.${table_name};
    CREATE TABLE IF NOT EXISTS ${KUDU_DATABASE}.${table_name}(
          tid bigint            COMMENT '通讯号',
          route_start_time bigint            COMMENT '车辆行程开始时间 1970/1/1 午夜距离该日期时间的秒数',
          pdt int     COMMENT 'yyyyMMdd 数据时间',
          trip_id bigint            COMMENT '行程ID',
          route_start_lc int      COMMENT '行程开始时所在区域编号',
          route_start_latitude int      COMMENT '车辆行程开始纬度 以度为单位的纬度值乘以 10 的 6 次方，精确到百万分之一度',
          route_start_longitude int      COMMENT '车辆行程开始经度 以度为单位的纬度值乘以 10 的 6 次方，精确到百万分之一度',
          route_start_height int      COMMENT '车辆行程开始高程 单位:M',
          route_start_direction int      COMMENT '车辆行程开始方向0-359正北为0顺时针',
          route_start_mileage int      COMMENT '车辆行程开始里程 单位:M',
          route_start_fuel int      COMMENT '车辆行程开始油耗 单位:ML',
          route_start_oilPercentage int      COMMENT '车辆行程起始油液百分比，扩展100倍，0200特有',
          route_end_time bigint            COMMENT '车辆行程结束时间 1970/1/1 午夜距离该日期时间的秒数',
          route_end_lc int      COMMENT '行程结束时所在区域编号',
          route_end_latitude int      COMMENT '车辆行程结束纬度 以度为单位的纬度值乘以 10 的 6 次方，精确到百万分之一度',
          route_end_longitude int      COMMENT '车辆行程结束经度 以度为单位的纬度值乘以 10 的 6 次方，精确到百万分之一度',
          route_end_height int      COMMENT '车辆行程结束高程 单位:M ',
          route_end_direction int      COMMENT '车辆行程结束方向0-359正北为0顺时针',
          route_end_mileage int      COMMENT '车辆行程结束里程 单位:M ',
          route_end_fuel int      COMMENT '车辆行程结束油耗 单位:ML ',
          route_end_oilPercentage int      COMMENT '车辆行程结束油液百分比，扩展100倍，0200特有',
          route_integral_mileage int      COMMENT '行程内积分里程（行程内车速积分里程） 单位:M',
          route_integral_fuel int      COMMENT '行程内积分油耗 单位:ML   ',
          stype int      COMMENT '行程数据切分类型(1:基于0f37数据来生成和统计行程 2:根据0200数据来切分行程和统计行程)',
          route_stop_flag int      COMMENT '行程结束标识 1:行程结束 0:行程未结束',
          cross_day_flag int      COMMENT '行程跨天标识 1:跨天 0:不跨天',
          force_end_flag int      COMMENT '行程强制结束标识（终端未上传行程结束的点） 1:强制结束 0:否',
          sharp_step_on_gas_number int      COMMENT '急踩油门次数',
          trip_power int      COMMENT '行程消耗功率单位:KWH',
          trip_mileage int      COMMENT '根据0200结果精准后的里程单位:M',
          trip_fuel int      COMMENT '根据0200结果精准后的油耗单位:ML',
          trip_duration int      COMMENT '行程时长单位:秒（行程结束时间-开始时间）=引擎工作时长engineWorkTime',
          max_useful_speed int      COMMENT '有效最高速度，单位：KM/H',
          min_useful_speed int      COMMENT '有效最小速度，单位：KM/H',
          in_gear_coast_mileage int      COMMENT '在挡滑行里程单位:M',
          in_gear_coast_duration int      COMMENT '在挡滑行时长单位：秒',
          in_gear_coast_number int      COMMENT '在挡滑行次数',
          brake_number int      COMMENT '制动次数',
          brake_mileage int      COMMENT '制动累计里程(制动总距离)单位:M',
          brake_duration int      COMMENT '制动时长单位:S',
          vehicle_estimated_load int      COMMENT '整车估算载荷单位:0.1吨',
          max_speed int      COMMENT '最大车速单位:KM/H',
          min_speed int      COMMENT '最小车速单位:KM/H',
          parking_power_on_duration int      COMMENT '停车通电时长单位:S',
          city_driving_mileage int      COMMENT '城市行驶里程单位:M',
          city_driving_duration int      COMMENT '城市行驶时长单位:S',
          city_driving_fuel int      COMMENT '城市行驶油量单位:ML',
          rural_driving_mileage int      COMMENT '非城市行驶里程单位:M',
          rural_driving_duration int      COMMENT '非城市行驶时长单位:S',
          rural_driving_fuel int      COMMENT '非城市行驶油量单位:ML',
          eco_driving_mileage int      COMMENT '经济行驶里程单位:M',
          eco_driving_duration int      COMMENT '"经济行驶时长单位:S',
          eco_driving_fuel int      COMMENT '经济行驶油量单位:ML',
          up_slope_driving_mileage int      COMMENT '上坡行驶距离单位:M',
          up_slope_driving_duration int      COMMENT '上坡行驶时长单位:S',
          up_slope_driving_fuel int      COMMENT '上坡行驶油量单位:ML',
          down_slope_driving_mileage int      COMMENT '下坡行驶距离单位:M',
          down_slope_driving_duration int      COMMENT '下坡行驶时长单位:S',
          down_slope_driving_fuel int      COMMENT '下坡行驶油量单位:ML',
          level_road_driving_mileage int      COMMENT '平路行驶距离单位:M',
          level_road_driving_duration int      COMMENT '平路行驶时长单位:S',
          level_road_driving_fuel int      COMMENT '平路行驶油量单位:ML',
          vehicle_cold_start_mileage int      COMMENT '冷车起步累计里程单位:M',
          vehicle_cold_start_duration int      COMMENT '冷车起步时长单位:S',
          vehicle_cold_start_fuel int      COMMENT '冷车起步耗油量单位:ML',
          cruise_mileage int      COMMENT '巡航里程(定速巡航距离)单位:M',
          cruise_duration int      COMMENT '定速巡航时长单位:S',
          cruise_fuel int      COMMENT '定速巡航油量单位:ML',
          shift_gears_number int      COMMENT '换挡次数',
          sharp_down_speed_duration int      COMMENT '激烈减速时长单位:S',
          sharp_down_speed_mileage int      COMMENT '激烈减速距离单位:M',
          sharp_down_speed_number int      COMMENT '激烈减速次数',
          sharp_up_speed_duration int      COMMENT '激烈加速时长单位:S',
          sharp_up_speed_mileage int      COMMENT '激烈加速距离单位:M',
          sharp_up_speed_number int      COMMENT '激烈加速次数',
          sharp_up_speed_fuel int      COMMENT '激烈加速油耗单位:ML',
          sharp_step_on_gas_mileage int      COMMENT '急踩油门行驶距离单位:M',
          sharp_step_on_gas_duration int      COMMENT '急踩油门时长单位:S',
          sharp_step_on_gas_fuel int      COMMENT '急踩油门油量单位:ML',
          vehicle_park_number int      COMMENT '车辆停车次数',
          vehicle_start_number int      COMMENT '车辆启步次数',
          vehicle_cold_start_number int      COMMENT '冷车启步次数',
          charge_fuel int      COMMENT '加油数量单位:ML',
          vehicle_start_duration int      COMMENT '车辆起步时长单位:S',
          vehicle_start_mileage int      COMMENT '车辆起步累计里程 单位:M',
          vehicle_start_fuel int      COMMENT '车辆起步耗油量单位:ML',
          gas_stability_number int      COMMENT '油门稳定性次数',
          gas_stability_duration int      COMMENT '油门稳定性时长单位:S',
          cross_cities string COMMENT '经过城市，逗号分隔',
          avg_speed int      COMMENT '平均速度',
          exceed_speed_range_mileage int      COMMENT '超区间车速(>120KM/H)累计里程单位:M',
          exceed_speed_range_duration int      COMMENT '超区间车速(>120KM/H)累计时长单位:S',
          exceed_speed_range_fuel int      COMMENT '超区间车速(>120KM/H)累计油耗单位:ML',
          assist_brake_mileage int      COMMENT '辅助制动里程单位:M',
          assist_brake_duration int      COMMENT '辅助制动时长单位:S',
          fatigue_driving_number int      COMMENT '疲劳驾驶次数',
          park_throttle_duration int      COMMENT '停车轰油门时长单位:S',
          park_throttle_fuel int      COMMENT '停车轰油门油耗单位:ML',
          engine_work_duration int      COMMENT '发动机工作时长单位:S',
          driving_handbrake_number int      COMMENT '行驶过程中手刹使用次数',
          driving_handbrake_duration int      COMMENT '行驶过程中手刹使用时长单位:S',
          driving_handbrake_mileage int      COMMENT '行驶过程中手刹使用里程单位:M',
          idle_handbrake_number int      COMMENT '怠速状态下手刹使用次数',
          idle_handbrake_duration int      COMMENT '怠速状态下手刹使用时长单位:S',
          sharp_turning_number int      COMMENT '急转弯次数',
          charge_fuel_percent int      COMMENT '加油量百分比 单位:百分比，范围:0~100',
          leak_fuel_percent int      COMMENT '漏油量百分比 单位:百分比，范围:0~100',
          leak_fuel int      COMMENT '漏油数量 单位:ML   ',
          fuel_100km int      COMMENT '百公里油耗',
          avg_speed_noidle int      COMMENT '平均车速(不含怠速)',
          eco_proportion int      COMMENT '经济区间占比',
          brake_times int      COMMENT '刹车次数',
          idle_fuel int      COMMENT '怠速油耗 单位:ML',
          idle_duration int      COMMENT '怠速时长 单位:S ',
          idle_number int      COMMENT '怠速次数   ',
          precise_flag int      COMMENT '是否精准里程油耗 1:精准 0:未精准 ',
          cross_cities_detail string COMMENT '经过城市详细信息',
          sharp_turning_mil int      COMMENT '急转弯行驶距离',
          fatigue_driving_duration int      COMMENT '疲劳驾驶时长',
          sharp_up_speed1_mileage int      COMMENT '东风急加速距离 单位:M',
          sharp_up_speed1_fuel int      COMMENT '东风急加速油耗 单位:ML',
          sharp_up_speed1_duration int      COMMENT '东风急加速时长 单位:S',
          sharp_up_speed1_number int      COMMENT '东风急加速次数',
          sharp_down_speed1_mileage int      COMMENT '东风急减速距离 单位:M',
          sharp_down_speed1_duration int      COMMENT '东风急减速时长 单位: S',
          sharp_down_speed1_number int      COMMENT '东风急减速次数',
          sharp_down_speed1_fuel int      COMMENT '东风急减速油耗 单位:ML',
          sharp_turning1_number int      COMMENT '东风急转弯次数',
          long_idle_fuel int      COMMENT '东风超长怠速油耗 单位:ML',
          long_idle_duration int      COMMENT '东风超长怠速时长 单位:S',
          long_idle_number int      COMMENT '东风超长怠速次数',
          cold_running_mileage int      COMMENT '东风冷车运行距离 单位:M',
          cold_running_fuel int      COMMENT '东风冷车运行油耗 单位:ML',
          cold_running_duration int      COMMENT '东风冷车运行时长，单位:S',
          cold_running_number int      COMMENT '东风冷车运行次数',
          night_running_mileage int      COMMENT '东风夜晚开车距离 单位:M',
          night_running_fuel int      COMMENT '东风夜晚开车油耗 单位:ML',
          night_running_duration int      COMMENT '东风夜晚开车时长 单位:S',
          night_running_number int      COMMENT '东风夜晚开车次数',
          high_rotation_mileage int      COMMENT '东风引擎高转速距离 单位:M',
          high_rotation_fuel int      COMMENT '东风引擎高转速油耗 单位:ML',
          high_rotation_duration int      COMMENT '东风引擎高转速时长 单位:S',
          high_rotation_number int      COMMENT '东风引擎高转速次数',
          large_gas_mileage int      COMMENT '东风大油门距离 单位:M',
          large_gas_fuel int      COMMENT '东风大油门油耗 单位:ML',
          large_gas_duration int      COMMENT '东风大油门时长 单位:S',
          large_gas_number int      COMMENT '东风大油门次数',
          full_gas_mileage int      COMMENT '东风全油门距离 单位:M',
          full_gas_fuel int      COMMENT '东风全油门油耗 单位:ML',
          full_gas_duration int      COMMENT '东风全油门时长 单位:S',
          full_gas_number int      COMMENT '东风全油门次数',
          fast_speed_mileage int      COMMENT '东风高速行驶距离 单位:M',
          fast_speed_fuel int      COMMENT '东风高速行驶油耗 单位:ML',
          fast_speed_duration int      COMMENT '东风高速行驶时长 单位:S',
          fast_speed_number int      COMMENT '东风高速行驶次数',
          neutral_gear_coast_duration int      COMMENT '空挡滑行时长 单位：秒',
          neutral_gear_coast_fuel int      COMMENT '空挡滑行油耗 单位:ML',
          neutral_gear_coast_mileage int      COMMENT '空挡滑行里程 单位:M',
          neutral_gear_coast_number int      COMMENT '空挡滑行次数',
          flameout_coast_mileage              int      COMMENT '东风熄火滑行距离 单位:M',
          flameout_coast_fuel                 int      COMMENT '东风熄火滑行油耗 单位:ML',
          flameout_coast_duration             int      COMMENT '东风熄火滑行时长 单位:S',
          flameout_coast_number               int      COMMENT '东风熄火滑行次数',
          so90_number int      COMMENT '超速90km/h次数',
          so90_duration int      COMMENT '超速90km/h时长',
          so90_mileage int      COMMENT '超速90km/h里程',
          so120_number int      COMMENT '超速120km/h次数',
          so120_duration int      COMMENT '超速120km/h时长',
          so120_mileage int      COMMENT '超速120km/h里程',
          so90_fuel int      COMMENT '超速90km/h油耗',
          so120_fuel int      COMMENT '超速120km/h油耗',
          totalScore int      COMMENT '总得分',
          safeScore int      COMMENT '安全得分',
          ecoScore int      COMMENT '经济得分',
          amt_load    int      comment '东风AMT载重 单位:0.1吨，2位小数',
          vecu_load   int      comment '东风VECU载重 单位:0.1吨，2位小数',
          load_type   int      comment '东风载重类型，0:空载 1:半载 2:满载 3:超载',
          update_time string COMMENT '修改时间',
          PRIMARY KEY(tid,route_start_time)
    )PARTITION BY HASH(tid) PARTITIONS 15,
    RANGE (route_start_time)(
        PARTITION 1609430400 <= VALUES < 1610208000,
        PARTITION 1610208000 <= VALUES < 1611504000,
        PARTITION 1611504000 <= VALUES < 1612800000,
        PARTITION 1612800000 <= VALUES < 1614096000,
        PARTITION 1614096000 <= VALUES < 1615392000,
        PARTITION 1615392000 <= VALUES < 1616688000,
        PARTITION 1616688000 <= VALUES < 1617984000,
        PARTITION 1617984000 <= VALUES < 1619280000,
        PARTITION 1619280000 <= VALUES < 1620576000,
        PARTITION 1620576000 <= VALUES < 1621872000,
        PARTITION 1621872000 <= VALUES < 1623168000,
        PARTITION 1623168000 <= VALUES < 1624464000,
        PARTITION 1624464000 <= VALUES < 1625760000,
        PARTITION 1625760000 <= VALUES < 1627056000,
        PARTITION 1627056000 <= VALUES < 1628352000,
        PARTITION 1628352000 <= VALUES < 1629648000,
        PARTITION 1629648000 <= VALUES < 1630944000,
        PARTITION 1630944000 <= VALUES < 1632240000,
        PARTITION 1632240000 <= VALUES < 1633536000,
        PARTITION 1633536000 <= VALUES < 1634832000,
        PARTITION 1634832000 <= VALUES < 1636128000,
        PARTITION 1636128000 <= VALUES < 1637424000,
        PARTITION 1637424000 <= VALUES < 1638720000,
        PARTITION 1638720000 <= VALUES < 1640016000,
        PARTITION 1640016000 <= VALUES < 1641312000,
        PARTITION 1641312000 <= VALUES < 1642608000,
        PARTITION 1642608000 <= VALUES < 1643904000,
        PARTITION 1643904000 <= VALUES < 1645200000,
        PARTITION 1645200000 <= VALUES < 1646496000,
        PARTITION 1646496000 <= VALUES < 1647792000,
        PARTITION 1647792000 <= VALUES < 1649088000,
        PARTITION 1649088000 <= VALUES < 1650384000,
        PARTITION 1650384000 <= VALUES < 1651680000,
        PARTITION 1651680000 <= VALUES < 1652976000,
        PARTITION 1652976000 <= VALUES < 1654272000,
        PARTITION 1654272000 <= VALUES < 1655568000,
        PARTITION 1655568000 <= VALUES < 1656864000,
        PARTITION 1656864000 <= VALUES < 1658160000,
        PARTITION 1658160000 <= VALUES < 1659456000,
        PARTITION 1659456000 <= VALUES < 1660752000,
        PARTITION 1660752000 <= VALUES < 1662048000,
        PARTITION 1662048000 <= VALUES < 1663344000,
        PARTITION 1663344000 <= VALUES < 1664640000,
        PARTITION 1664640000 <= VALUES < 1665936000,
        PARTITION 1665936000 <= VALUES < 1667232000,
        PARTITION 1667232000 <= VALUES < 1668528000,
        PARTITION 1668528000 <= VALUES < 1669824000,
        PARTITION 1669824000 <= VALUES < 1671120000,
        PARTITION 1671120000 <= VALUES < 1672416000,
        PARTITION 1672416000 <= VALUES < 1673712000,
        PARTITION 1673712000 <= VALUES < 1675008000,
        PARTITION 1675008000 <= VALUES < 1676304000,
        PARTITION 1676304000 <= VALUES < 1677600000,
        PARTITION 1677600000 <= VALUES < 1678896000,
        PARTITION 1678896000 <= VALUES < 1680192000,
        PARTITION 1680192000 <= VALUES < 1681488000,
        PARTITION 1681488000 <= VALUES < 1682784000,
        PARTITION 1682784000 <= VALUES < 1684080000,
        PARTITION 1684080000 <= VALUES < 1685376000,
        PARTITION 1685376000 <= VALUES < 1686672000,
        PARTITION 1686672000 <= VALUES < 1687968000,
        PARTITION 1687968000 <= VALUES < 1689264000,
        PARTITION 1689264000 <= VALUES < 1690560000,
        PARTITION 1690560000 <= VALUES < 1691856000,
        PARTITION 1691856000 <= VALUES < 1693152000,
        PARTITION 1693152000 <= VALUES < 1694448000,
        PARTITION 1694448000 <= VALUES < 1695744000,
        PARTITION 1695744000 <= VALUES < 1697040000,
        PARTITION 1697040000 <= VALUES < 1698336000,
        PARTITION 1698336000 <= VALUES < 1699632000,
        PARTITION 1699632000 <= VALUES < 1700928000,
        PARTITION 1700928000 <= VALUES < 1702224000,
        PARTITION 1702224000 <= VALUES < 1703520000,
        PARTITION 1703520000 <= VALUES < 1704816000

)
    STORED AS KUDU
    TBLPROPERTIES (
        'kudu.master_addresses' = '${KUDU_MASTER}',
        'kudu.num_tablet_replicas'='1'
    );
EOF
)

impala-shell -i ${IMPALA_HOST} -d ${KUDU_DATABASE} -q "$create_table_sql"
if [ $? -ne 0 ] ; then
  echo "${table_name}表创建失败"
else
  echo "${table_name}表创建成功"
fi
#2020-01-01 00:00:00 分区开始